﻿/// Copyright 2011 - Agus Kurniawan
/// Blog: http://blog.aguskurniawan.net 
/// ====================================================================

using System;
using System.Collections.Generic;
using System.Reflection;
using System.IO;
using System.Data.SQLite;

namespace Galunggung
{
    public class SqlMacAddress
    {
        private string _dbFile = string.Empty;        

        private SqlMacAddress()
        {
        }
        public static SqlMacAddress Create()
        {
            SqlMacAddress o = new SqlMacAddress();
            string path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            if (File.Exists(string.Format("{0}\\macs.db",path)))
                o._dbFile = string.Format("{0}\\macs.db",path);            

            return o;
        }

        private SQLiteConnection GetConnection()
        {
            SQLiteConnection con = new SQLiteConnection(string.Format("Data Source={0}",_dbFile));
            con.Open();
            return con;
        }

        public List<NicInfo> GetAllNic()
        {
            List<NicInfo> list = new List<NicInfo>();
            SQLiteConnection con = GetConnection();
            SQLiteCommand cmd = new SQLiteCommand("select * from nic", con);

            SQLiteDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                NicInfo o = new NicInfo();
                o.Id = (string)rd["nic_id"];
                o.RegId = Convert.ToString(rd["reg_code"]);
                o.Name = (string)rd["nic_name"];
                o.Description = (string)rd["nic_des"];
                o.MACAdress = Convert.ToString(rd["mac_address"]);
                o.SpoofedMACAdress = Convert.ToString(rd["spoofed_mac_address"]);
                o.SpoofedMACAdressId = Convert.ToInt32(rd["spoofed_mac_id"]);

                list.Add(o);
            }
            rd.Close();
            con.Close();

            return list;
        }
        public List<NicInfo> GetAllMacs()
        {
            List<NicInfo> list = new List<NicInfo>();
            SQLiteConnection con = GetConnection();
            SQLiteCommand cmd = new SQLiteCommand("select * from mac_address", con);

            SQLiteDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                NicInfo o = new NicInfo();
                o.SpoofedMACAdressId = Convert.ToInt32(rd["mac_id"]);
                o.SpoofedMACAdress = Convert.ToString(rd["mac_address"]);

                list.Add(o);
            }
            rd.Close();
            con.Close();

            return list;
        }
        public NicInfo GetNic(string id)
        {
            NicInfo o = null;
            string sql = "select * from nic where nic_id=@nic_id";
            SQLiteConnection con = GetConnection();
            SQLiteCommand cmd = new SQLiteCommand(sql, con);

            SQLiteParameter pars = new SQLiteParameter("@nic_id", id);
            cmd.Parameters.Add(pars);

            SQLiteDataReader rd = cmd.ExecuteReader();
            if (rd.Read())
            {
                o = new NicInfo();
                o.Id = (string)rd["nic_id"];
                o.RegId = Convert.ToString(rd["reg_code"]);
                o.Name = (string)rd["nic_name"];
                o.Description = (string)rd["nic_des"];
                o.MACAdress = Convert.ToString(rd["mac_address"]);
                o.SpoofedMACAdress = Convert.ToString(rd["spoofed_mac_address"]);
                o.SpoofedMACAdressId = Convert.ToInt32(rd["spoofed_mac_id"]);

            }
            rd.Close();
            con.Close();

            return o;
        }
        public bool InsertNic(NicInfo o)
        {            
            string sql = @"insert into nic(nic_id,reg_code,nic_name,mac_address,
                            spoofed_mac_address,spoofed_mac_id,nic_des) values
                            (@nic_id,@reg_code,@nic_name,@mac_address,
                            @spoofed_mac_address,@spoofed_mac_id,@nic_des)";

            try
            {
                SQLiteConnection con = GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);

                SQLiteParameter[] pars = new SQLiteParameter[7];
                pars[0] = new SQLiteParameter("@nic_id", o.Id);
                pars[1] = new SQLiteParameter("@reg_code", o.RegId);
                pars[2] = new SQLiteParameter("@nic_name", o.Name);
                pars[3] = new SQLiteParameter("@mac_address", o.MACAdress);
                pars[4] = new SQLiteParameter("@spoofed_mac_address", o.SpoofedMACAdress);
                pars[5] = new SQLiteParameter("@spoofed_mac_id", o.SpoofedMACAdressId);
                pars[6] = new SQLiteParameter("@nic_des", o.Description);

                cmd.Parameters.AddRange(pars);
                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (Exception err)
            {
                System.Diagnostics.Debug.WriteLine(err.Message);
                return false;
            }

            return true;
        }

        public bool UpdateNicHardware(NicInfo o)
        {
            string sql = @"update nic set reg_code=@reg_code,nic_name=@nic_name,mac_address=@mac_address, 
                        nic_des =@nic_des where nic_id = @nic_id";

            try
            {
                SQLiteConnection con = GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);

                SQLiteParameter[] pars = new SQLiteParameter[5];
                pars[0] = new SQLiteParameter("@nic_id", o.Id);
                pars[1] = new SQLiteParameter("@reg_code", o.RegId);
                pars[2] = new SQLiteParameter("@nic_name", o.Name);
                pars[3] = new SQLiteParameter("@mac_address", o.MACAdress);
                pars[4] = new SQLiteParameter("@nic_des", o.Description);

                cmd.Parameters.AddRange(pars);
                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (Exception err) 
            {
                System.Diagnostics.Debug.WriteLine(err.Message);
                return false; 
            }

            return true;
        }

        public bool UpdateNicSpoofed(NicInfo o)
        {
            string sql = @"update nic set spoofed_mac_address=@spoofed_mac_address,spoofed_mac_id=@spoofed_mac_id 
                         where nic_id = @nic_id";

            try
            {
                SQLiteConnection con = GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);

                SQLiteParameter[] pars = new SQLiteParameter[3];
                pars[0] = new SQLiteParameter("@nic_id", o.Id);
                pars[1] = new SQLiteParameter("@spoofed_mac_address", o.SpoofedMACAdress);
                pars[2] = new SQLiteParameter("@spoofed_mac_id", o.SpoofedMACAdressId);

                cmd.Parameters.AddRange(pars);
                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (Exception err)
            {
                System.Diagnostics.Debug.WriteLine(err.Message);
                return false;
            }

            return true;
        }
        public bool InsertMacAddress(string macAddress)
        {
            string sql = @"insert into mac_address(mac_address) values
                            (@mac_address)";

            try
            {
                SQLiteConnection con = GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);

                SQLiteParameter pars = new SQLiteParameter("@mac_address", macAddress);                

                cmd.Parameters.Add(pars);
                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (Exception) { return false; }

            return true;
        }

        public bool DeleteMacAddress(int id)
        {
            string sql = @"delete from mac_address where mac_id=@mac_id";

            try
            {
                SQLiteConnection con = GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);

                SQLiteParameter pars = new SQLiteParameter("@mac_id", id);

                cmd.Parameters.Add(pars);
                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (Exception) { return false; }

            return true;
        }
    }
}
